package com.boot.unity.api.modules.rzgjj.dao;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface GjjDao {
    // 银行卡的有效性查询
    @Select("SELECT * FROM (SELECT g.SA_CUST_CERT_NO AS codeno,g.SA_CUST_NAME AS pername,g.CI_MOBILE_PHONE AS phone,g.SA_CUST_CERT_TYP AS codetype,g.SA_CARD_NO  AS bankcode,g.CI_ACCT_TYP AS accounttype,(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id,'313008' AS banktype,'01' AS rescode,'查询正常' AS retmsg,ROW_NUMBER() OVER(ORDER BY g.SA_CUST_NAME) AS rownum " +
            "FROM GJJYHK g where 1=1 " +
            " and g.SA_CUST_NAME = #{pername} AND g.SA_CUST_CERT_TYP = #{codetype} AND g.SA_CUST_CERT_NO =#{codeno}" +
            " and g.SA_CARD_NO = #{bankcode} ) t WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBAS001Data(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("bankcode") String bankcode,@Param("accounttype") String accounttype,@Param("startNum") int startNum,@Param("endNum") int endNum);

    @Select("SELECT count(1) FROM GJJYHK g where 1=1" +
            " and g.SA_CUST_NAME = #{pername} AND g.SA_CUST_CERT_TYP = #{codetype} AND g.SA_CUST_CERT_NO =#{codeno}" +
            " and g.SA_CARD_NO = #{bankcode}")
    int queryBAS001Count(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("bankcode") String bankcode,@Param("accounttype") String accounttype);




    /*// 商业银行贷款合同信息
    @Select("select * from (SELECT g.LOANDATE  AS bdate,0 AS ratemny,g.\"limit\" AS loanmnhs,g.BALAMT AS loanbal," +
            "g.LOANACCOUNT AS loancode,g.INTEREST AS loanrate,g.CONTRACTNO AS agrcode,0 AS housearea,0 AS mntpay," +
            "0 AS payday,g.ACCOUNTSTATE AS loanstate,g.CONTRACTMONEY AS loanmny,0 AS fpaymny,0 AS housemny,g.CNAME AS pername," +
            "0 AS basemny,g.ORIENDDATE AS edate,TO_CHAR(CURRENT DATE ,'YYYYMMDD') AS day_id,g.REPAYTYPE AS repayway,g.CUSTIDNO  AS cardcode," +
            "g.CUSTIDTYPE," +
            "ROW_NUMBER() OVER(ORDER BY g.CUSTIDNO) AS rownum FROM GJJHT g " +
            " WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}) t " +
            "WHERE rownum BETWEEN #{startNum} AND #{endNum}")*/

    // 商业银行贷款合同信息
    /*@Select("select * from (SELECT g.LOANDATE  AS bdate,(SELECT COALESCE(sum(g2.LN_INT),0) FROM GJJLX g2 WHERE g.LOANACCOUNT = g2.LN_LN_ACCT_NO) AS ratemny,'0' AS loanmnhs,g.BALAMT AS loanbal," +
            "g.LOANACCOUNT AS loancode,g.INTEREST AS loanrate,g.CONTRACTNO AS agrcode,0 AS housearea,0 AS mntpay," +
            "0 AS payday,g.ACCOUNTSTATE AS loanstate,g.CONTRACTMONEY AS loanmny,0 AS fpaymny,0 AS housemny,g.CNAME AS pername," +
            "((SELECT COALESCE(sum(g2.LN_TX_AMT),0) FROM GJJBJ g2 WHERE (g2.LN_DR_CR_COD='C' or (g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001'))) AND g.LOANACCOUNT = g2.LN_LN_ACCT_NO)) AS basemny" +
            ",g.ORIENDDATE AS edate,(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id,g.REPAYTYPE AS repayway,g.CUSTIDNO  AS cardcode_2," +
            "g.CUSTIDTYPE AS codetype," +
            "ROW_NUMBER() OVER(ORDER BY g.CUSTIDNO) AS rownum FROM GJJHT g " +
            " WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}) t " +
            "WHERE rownum BETWEEN #{startNum} AND #{endNum}")  */
    // 商业银行贷款合同信息
    @Select("select * from (SELECT g.LOANDATE  AS bdate," +
            "((SELECT COALESCE(sum(g2.LN_INT),0) FROM GJJLX g2 WHERE substr(g2.ln_tx_no,4,4) in('3003','3004') AND g2.ln_dr_cr_cod='C' and g.LOANACCOUNT = g2.LN_LN_ACCT_NO)" +
            "-(SELECT COALESCE(sum(g2.LN_INT),0) FROM GJJLX g2 WHERE substr(g2.ln_tx_no,4,4) in('3004') AND g2.ln_dr_cr_cod='D' and g.LOANACCOUNT = g2.LN_LN_ACCT_NO)) AS ratemny," +
            "g.limit AS loanmnhs,g.BALAMT AS loanbal," +
            "g.LOANACCOUNT AS loancode,g.INTEREST AS loanrate,g.CONTRACTNO AS agrcode,0 AS housearea,0 AS mntpay," +
            "g.PPRDINTPDAY AS payday,g.ACCOUNTSTATE AS loanstate,g.CONTRACTMONEY AS loanmny,0 AS fpaymny,0 AS housemny,g.CNAME AS pername," +
            "(((SELECT COALESCE(sum(g2.LN_TX_AMT),0) FROM GJJBJ g2 WHERE g2.LN_DR_CR_COD='C' AND g.LOANACCOUNT = g2.LN_LN_ACCT_NO)-" +
            "(SELECT COALESCE(sum(g2.LN_TX_AMT),0) FROM GJJBJ g2 WHERE g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001') AND g.LOANACCOUNT = g2.LN_LN_ACCT_NO))) AS basemny" +
            ",g.ORIENDDATE AS edate,(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id,g.REPAYTYPE AS repayway,g.CUSTIDNO  AS cardcode_2," +
            "g.CUSTIDTYPE AS codetype," +
            "ROW_NUMBER() OVER(ORDER BY g.CUSTIDNO) AS rownum FROM GJJHT g " +
            " WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}) t " +
            "WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBLS001Data(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("startNum") int startNum,@Param("endNum") int endNum);

    @Select("SELECT count(1) FROM GJJHT g WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}")
    int queryBLS001Count(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno);




    //BLS002   获取商业贷款偿还金额
    /*@Select("select count(1) from GJJCHJE g,GJJYQLX x WHERE g.LOANACCOUNT = x.LN_LN_ACCT_NO  AND " +
            "date(to_date(x.DATA_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and g.contractno = #{agrcode}")*/
    // 使用记账日期
    /*@Select("SELECT count(1) " +
            "FROM GJJCHJE j,GJJYQLX y,GJJLJBJ b,GJJLJLX x WHERE  J.CONTRACTNO = b.CONTRACTNO AND j.LOANACCOUNT  = y.LN_LN_ACCT_NO " +
            "AND j.CONTRACTNO  = x.CONTRACTNO AND " +
            "date(to_date(y.DATA_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and j.contractno = #{agrcode}")
    int queryBAS002Count(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate,@Param("enddate") String enddate);

    @Select("SELECT * FROM(SELECT J.CUSTIDNO AS cardcode,j.CNAME AS pername ,j.CUSTIDTYPE AS codetype,j.LOANACCOUNT AS loancode," +
            "(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id,j.CONTRACTNO AS agrcode,j.YHQS AS payissues, " +
            "(b.LJCHBJ + y.LN_CRNT_DLAY_INT + x.LJCHLX) AS payedmny,ROW_NUMBER() OVER(ORDER BY j.CNAME) AS rownum " +
            "FROM GJJCHJE j,GJJYQLX y,GJJLJBJ b,GJJLJLX x WHERE  J.CONTRACTNO = b.CONTRACTNO AND j.LOANACCOUNT  = y.LN_LN_ACCT_NO " +
            "AND j.CONTRACTNO  = x.CONTRACTNO AND " +
            "date(to_date(y.DATA_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and j.contractno = #{agrcode}) WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBAS002Data(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate, @Param("startNum")int startNum, @Param("endNum")int endNum);
*/


    // SELECT count(DISTINCT LN_TNRNO) FROM GJJBJ  保证数据条数对应起来
    /*@Select("SELECT  g.CUSTIDNO AS cardcode,g.CNAME AS pername,g.CUSTIDTYPE AS codetype,g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS loancode," +
            "(SELECT count(LN_TNRNO) FROM GJJBJ a WHERE (a.LN_DR_CR_COD='C' or (a.LN_DR_CR_COD='D' and a.LN_TNRNO NOT in('0001','0000','000','001'))) AND date(to_date(a.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and a.LN_LN_ACCT_NO = g.LOANACCOUNT) AS payissues,"+

            "((SELECT COALESCE(sum(g2.LN_TX_AMT),0) FROM GJJBJ g2 WHERE (g2.LN_DR_CR_COD='C' or (g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001'))) AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g2.LN_LN_ACCT_NO = g.LOANACCOUNT) +" +
            "(SELECT COALESCE(sum(g3.LN_INT),0) FROM GJJLX g3 WHERE date(to_date(g3.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g3.LN_LN_ACCT_NO = g.LOANACCOUNT)) AS payedmny," +

            "(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id FROM GJJHT g " +
            "where  g.CONTRACTNO = #{agrcode}")*/
    @Select("SELECT  g.CUSTIDNO AS cardcode,g.CNAME AS pername,g.CUSTIDTYPE AS codetype,g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS loancode," +
            "(SELECT count(LN_TNRNO) FROM GJJBJ a WHERE (a.LN_DR_CR_COD='C' or (a.LN_DR_CR_COD='D' and a.LN_TNRNO NOT in('0001','0000','000','001'))) AND date(to_date(a.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and a.LN_LN_ACCT_NO = g.LOANACCOUNT) AS payissues,"+
            "(" +
            "((SELECT COALESCE(sum(g2.LN_TX_AMT),0) FROM GJJBJ g2 WHERE g2.LN_DR_CR_COD='C' AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g.LOANACCOUNT = g2.LN_LN_ACCT_NO)" +
            "-(SELECT COALESCE(sum(g3.LN_TX_AMT),0) FROM GJJBJ g3 WHERE g3.LN_DR_CR_COD='D' and g3.LN_TNRNO NOT in('0001','0000','000','001') AND date(to_date(g3.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g.LOANACCOUNT = g3.LN_LN_ACCT_NO))" +
            "+((SELECT COALESCE(sum(g4.LN_INT),0) FROM GJJLX g4 WHERE substr(g4.ln_tx_no,4,4) in('3003','3004') AND g4.ln_dr_cr_cod='C' and date(to_date(g4.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g.LOANACCOUNT = g4.LN_LN_ACCT_NO)" +
            "-(SELECT COALESCE(sum(g5.LN_INT),0) FROM GJJLX g5 WHERE substr(g5.ln_tx_no,4,4) in('3004') AND g5.ln_dr_cr_cod='D' and date(to_date(g5.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) and g.LOANACCOUNT = g5.LN_LN_ACCT_NO))" +
            ") AS payedmny," +
            "(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id FROM GJJHT g " +
            "where  g.CONTRACTNO = #{agrcode}")
    List<JSONObject> queryBAS002Data_New(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate);

    @Select("select * FROM GJJHT g where g.CONTRACTNO = #{agrcode}")
    List<JSONObject> queryHtData(@Param("agrcode")String agrcode);



    //BLS003  获取商业贷款还款记录
    /*@Select("SELECT COUNT(1) FROM GJJCHJE g,GJJBJ j WHERE g.LOANACCOUNT = j.LN_LN_ACCT_NO AND" +
            " date(to_date(j.DATA_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and g.contractno = #{agrcode}")*/
    // 使用记账日期
    /*@Select("SELECT count(1) FROM GJJCHJE j,GJJHT h ,GJJBJ g,GJJLX x ,GJJYQLX lx WHERE   j.CUSTIDNO = h.CUSTIDNO " +
            "AND J.LOANACCOUNT = g.LN_LN_ACCT_NO AND j.LOANACCOUNT  =  x.LN_LN_ACCT_NO AND g.LN_TNRNO = x.LN_TNRNO  AND g.LN_LN_ACCT_NO  = lx.LN_LN_ACCT_NO  AND g.LN_TNRNO  = lx.LN_TNRNO " +
            " AND date(to_date(x.LN_ENTR_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and j.contractno = #{agrcode} ")
    int queryBAS003Count(@Param("cardcode") String cardcode,@Param("pername") String pername, @Param("agrcode") String agrcode, @Param("begindate")String begindate,@Param("enddate") String enddate);

    @Select("SELECT * FROM(SELECT j.CUSTIDNO AS cardcode,j.CNAME AS pername,j.CUSTIDTYPE AS codetype,j.LOANACCOUNT AS loancode,(SELECT MAX(DATA_DT)  FROM GJJDATA) AS day_id,j.CONTRACTNO AS bkloanacc, h.INTEREST AS loanrate ,g.LN_TNRNO AS mntindx,VARCHAR_FORMAT(g.DATA_DT,'YYYYMM') AS paymnt " +
            ", VARCHAR_FORMAT(g.LN_ENTR_DT ,'YYYY-MM-DD') AS bkdate,g.LN_TX_AMT_PR AS basemny,x.LN_INT AS ratemny,lx.LN_CRNT_DLAY_INT AS moremny,LX.LN_LN_BAL AS loanbal" +
            ",CASE WHEN VARCHAR_FORMAT(g.LN_ENTR_DT, 'DD')  > h.PPRDINTPDAY THEN '逾期还款'" +
            "      WHEN VARCHAR_FORMAT(g.LN_ENTR_DT, 'DD')  < h.PPRDINTPDAY THEN '正常还款'" +
            "    END AS paytype ,ROW_NUMBER() OVER(ORDER BY j.CNAME) AS rownum FROM GJJCHJE j,GJJHT h ,GJJBJ g,GJJLX x ,GJJYQLX lx WHERE   j.CUSTIDNO = h.CUSTIDNO " +
            "AND J.LOANACCOUNT = g.LN_LN_ACCT_NO AND j.LOANACCOUNT  =  x.LN_LN_ACCT_NO AND g.LN_TNRNO = x.LN_TNRNO  AND g.LN_LN_ACCT_NO  = lx.LN_LN_ACCT_NO  AND g.LN_TNRNO  = lx.LN_TNRNO " +
            " AND date(to_date(x.LN_ENTR_DT,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD'))" +
            " and j.contractno = #{agrcode})WHERE rownum BETWEEN #{startNum} AND #{endNum} ")
    List<JSONObject> queryBAS003Data(@Param("cardcode") String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate, @Param("startNum")int startNum, @Param("endNum")int endNum);

*/

    // 新--------------------------------------------BLS003  获取商业贷款还款记录-------------------------------------------------
    @Select("SELECT count(1) FROM GJJHT g,GJJBJ g2 WHERE (g2.LN_DR_CR_COD='C' or (g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001'))) AND g.LOANACCOUNT = g2.LN_LN_ACCT_NO AND " +
            "date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND " +
            "date(to_date(#{enddate},'YYYY-MM-DD')) AND g.CONTRACTNO = #{agrcode}")
    int queryBLS003NewCount(@Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate);
    // 进行获取合同信息+本金信息的基础数据
    /*@Select("select * from (SELECT g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS bkloanacc,g.CUSTIDNO AS cardcode,g.CUSTIDTYPE AS codetype,g.CNAME AS pername," +
            "(SELECT MAX(DATA_DT) FROM GJJDATA) AS day_id,g2.LN_TNRNO AS mntindx,substr(g2.DATA_DT,1,6) AS paymnt,(substr(g2.LN_ENTR_DT,1,4)||'-'||substr(g2.LN_ENTR_DT,5,2)||'-'||substr(g2.LN_ENTR_DT,7,2)) AS bkdate," +
            "g2.LN_TX_AMT AS basemny,g.INTEREST AS loanrate,g2.LN_ATX_BAL AS loanbal," +
            "(SELECT sum(g3.LN_INT) FROM GJJLX g3 WHERE g3.LN_DR_CR_COD in('4','6') AND g3.LN_TNRNO = g2.LN_TNRNO AND g3.LN_LN_ACCT_NO = g2.LN_LN_ACCT_NO) AS moremny," +
            "(SELECT sum(g3.LN_INT) FROM GJJLX g3 WHERE g3.LN_DR_CR_COD not in('4','6') AND g3.LN_TNRNO = g2.LN_TNRNO AND g3.LN_LN_ACCT_NO = g2.LN_LN_ACCT_NO) AS ratemny,ROW_NUMBER() OVER(ORDER BY g.CNAME) AS rownum " +
            "FROM GJJHT g,GJJBJ g2 WHERE g.LOANACCOUNT = g2.LN_LN_ACCT_NO AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) AND g.CONTRACTNO = #{agrcode}) WHERE rownum BETWEEN #{startNum} AND #{endNum}")*/
    /*@Select("select * from (SELECT g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS bkloanacc,g.CUSTIDNO AS cardcode,g.CUSTIDTYPE AS codetype," +
            "g.CNAME AS pername,(SELECT MAX(DATA_DT) FROM GJJDATA) AS day_id,g2.LN_TNRNO AS mntindx,substr(g2.DATA_DT,1,6) AS paymnt," +
            "(substr(g2.LN_ENTR_DT,1,4)||'-'||substr(g2.LN_ENTR_DT,5,2)||'-'||substr(g2.LN_ENTR_DT,7,2)) AS bkdate,g2.LN_TX_AMT AS basemny," +
            "g.INTEREST AS loanrate,g2.LN_ATX_BAL AS loanbal,ROW_NUMBER() OVER(ORDER BY g.CNAME) AS rownum " +
            "FROM GJJHT g,GJJBJ g2 WHERE (g2.LN_DR_CR_COD='C' or (g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001'))) AND " +
            "g.LOANACCOUNT = g2.LN_LN_ACCT_NO AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) " +
            "BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) AND " +
            "g.CONTRACTNO = #{agrcode} ORDER BY g2.LN_TNRNO DESC) WHERE rownum BETWEEN #{startNum} AND #{endNum}")*/


    @Select("select * from (" +
            "SELECT t.*,ROW_NUMBER() OVER(ORDER BY t.pername) AS rownum FROM " +
            "((SELECT g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS bkloanacc,g.CUSTIDNO AS cardcode,g.CUSTIDTYPE AS codetype," +
            "g.CNAME AS pername,(SELECT MAX(DATA_DT) FROM GJJDATA) AS day_id,g2.LN_TNRNO AS mntindx,substr(g2.DATA_DT,1,6) AS paymnt," +
            "(substr(g2.LN_ENTR_DT,1,4)||'-'||substr(g2.LN_ENTR_DT,5,2)||'-'||substr(g2.LN_ENTR_DT,7,2)) AS bkdate,g2.LN_TX_AMT AS basemny," +
            "g.INTEREST AS loanrate,g2.LN_ATX_BAL AS loanbal " +
            "FROM GJJHT g,GJJBJ g2 WHERE g2.LN_DR_CR_COD='C' AND " +
            "g.LOANACCOUNT = g2.LN_LN_ACCT_NO AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) " +
            "BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) AND " +
            " g.CONTRACTNO = #{agrcode} " +
            "UNION " +
            "SELECT g.CONTRACTNO AS agrcode,g.LOANACCOUNT AS bkloanacc,g.CUSTIDNO AS cardcode,g.CUSTIDTYPE AS codetype," +
            "g.CNAME AS pername,(SELECT MAX(DATA_DT) FROM GJJDATA) AS day_id,g2.LN_TNRNO AS mntindx,substr(g2.DATA_DT,1,6) AS paymnt," +
            "(substr(g2.LN_ENTR_DT,1,4)||'-'||substr(g2.LN_ENTR_DT,5,2)||'-'||substr(g2.LN_ENTR_DT,7,2)) AS bkdate,g2.LN_TX_AMT*-1 AS basemny," +
            "g.INTEREST AS loanrate,g2.LN_ATX_BAL AS loanbal " +
            " FROM GJJHT g,GJJBJ g2 WHERE g2.LN_DR_CR_COD='D' and g2.LN_TNRNO NOT in('0001','0000','000','001') AND " +
            "g.LOANACCOUNT = g2.LN_LN_ACCT_NO AND date(to_date(g2.LN_ENTR_DT ,'YYYY-MM-DD')) " +
            "BETWEEN date(to_date(#{begindate},'YYYY-MM-DD')) AND date(to_date(#{enddate},'YYYY-MM-DD')) AND " +
            " g.CONTRACTNO = #{agrcode} )) t ORDER BY t.mntindx DESC" +
            ") WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBLS003NewData(@Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate, @Param("startNum")int startNum, @Param("endNum")int endNum);

    // 通过贷款账号和日期，进行计算利息
    /*@Select("SELECT sum(g.LN_INT) FROM GJJLX g WHERE g.LN_DR_CR_COD NOT in('4','6') " +
            "and g.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g.LN_LN_ACCT_NO = #{accNo} ")*/
    @Select("SELECT (" +
            "(SELECT COALESCE(sum(g.LN_INT),0) FROM GJJLX g WHERE substr(g.ln_tx_no,4,4) in('3003','3004') AND g.ln_dr_cr_cod='C' and g.LN_INT_TYP not in ('4','6') and g.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g.LN_LN_ACCT_NO = #{accNo})" +
            "-(SELECT COALESCE(sum(g2.LN_INT),0) FROM GJJLX g2 WHERE substr(g2.ln_tx_no,4,4) in('3004') AND g2.ln_dr_cr_cod='D' and g2.LN_INT_TYP not in ('4','6') and g2.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g2.LN_LN_ACCT_NO = #{accNo})" +
            ") AS lxmny FROM SYSIBM.SYSDUMMY1")
    String queryBLS003Lx(@Param("accNo") String accNo,@Param("ymrq") String ymrq);

    // 通过贷款账号和日期，进行计算罚息
    /*@Select("SELECT sum(g.LN_INT) FROM GJJLX g WHERE g.LN_DR_CR_COD in('4','6') " +
            "and g.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g.LN_LN_ACCT_NO = #{accNo}")*/
    @Select("SELECT (" +
            "(SELECT COALESCE(sum(g.LN_INT),0) FROM GJJLX g WHERE substr(g.ln_tx_no,4,4) in('3003','3004') AND g.ln_dr_cr_cod='C' and g.LN_INT_TYP in ('4','6') and g.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g.LN_LN_ACCT_NO = #{accNo})" +
            "-(SELECT COALESCE(sum(g2.LN_INT),0) FROM GJJLX g2 WHERE substr(g2.ln_tx_no,4,4) in('3004') AND g2.ln_dr_cr_cod='D' and g2.LN_INT_TYP not in ('4','6') and g2.LN_ENTR_DT LIKE (#{ymrq}||'%') AND g2.LN_LN_ACCT_NO = #{accNo})" +
            ") AS lxmny FROM SYSIBM.SYSDUMMY1")
    String queryBLS003Fx(@Param("accNo") String accNo,@Param("ymrq") String ymrq);
}
